class CountController < UserController
  def index
    #Score.select("department_id, sum(amount) as amount, sum(score) as score, id_type").group(:id_type)
    #@q = Score.joins(:department).select("master_id, dpname, sum(case id_type when 0 then amount else 0 end) as amount0, sum(case id_type when 0 then score else 0 end) as score0, sum(case id_type when 1 then amount else 0 end) as amount1, sum(case id_type when 1 then score else 0 end) as score1, sum(case id_type when 2 then amount else 0 end) as amount2, sum(case id_type when 2 then score else 0 end) as score2, id_type").group(:id_type, :master_id, :dpname).by_user(current_user).ransack(params[:q])
    #@q = Score.joins('right outer join departments on departments.id = scores.department_id').select('dpname, sum(case id_type when 0 then amount else 0 end) as amount0, sum(case id_type when 0 then score else 0 end) as score0, sum(case id_type when 1 then amount else 0 end) as amount1, sum(case id_type when 1 then score else 0 end) as score1, sum(case id_type when 2 then amount else 0 end) as amount2, sum(case id_type when 2 then score else 0 end) as score2').group(:id_type, :dpname).order('departments.id')
    #@scores = Score.joins('right outer join departments on departments.id = scores.department_id')
    #            .select('dpname, level, sum(case id_type when 0 then amount else 0 end) as amount0, sum(case id_type when 0 then score else 0 end) as score0, sum(case id_type when 1 then amount else 0 end) as amount1, sum(case id_type when 1 then score else 0 end) as score1, sum(case id_type when 2 then amount else 0 end) as amount2, sum(case id_type when 2 then score else 0 end) as score2')
    #            .count_by_user(current_user)
    #            .group(:department_id, :id_type)
    #            .order('departments.id')
    @scores = Score.joins('right outer join departments on departments.id = scores.department_id')
                .select('dpname, sum(case id_type_id when 0 then amount else 0 end) as amount0, sum(case id_type_id when 0 then score else 0 end) as score0, sum(case id_type_id when 1 then amount else 0 end) as amount1, sum(case id_type_id when 1 then score else 0 end) as score1, sum(case id_type_id when 2 then amount else 0 end) as amount2, sum(case id_type_id when 2 then score else 0 end) as score2')
                .count_by_user(current_user, params[:master_id])
    #@scores = Score.joins(:bus_type).joins('right outer join departments on departments.id = scores.department_id')
    #            .select('dpname, sum(case id_type_id when 0 then amount else 0 end) as amount0, sum(case id_type_id when 0 then score else 0 end) as score0, sum(case id_type_id when 1 then amount else 0 end) as amount1, sum(case id_type_id when 1 then score else 0 end) as score1, sum(case id_type_id when 2 then amount else 0 end) as amount2, sum(case id_type_id when 2 then score else 0 end) as score2')
  end
end
